package com.yulian.web_api.supplier_management;

import com.healthmarketscience.sqlbuilder.BinaryCondition;
import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.repository.CommonRepositoryImpl;
import com.yulian.util.CommonUtils;
import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.auth.User;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.Tuple;

import java.util.*;

/**
 * 该类位于业务逻辑层，主要实现商品名系统参数页面的数据查询，新增数据，更新数据，删除数据、导入、打印数据的业务逻辑，为MainVerticle中的REST接口提供服务
 *
 * @author 汪俊峰 刘涛 余大勇
 */
public class GoodsParameterManager {

    final InternalLogger logger = Log4JLoggerFactory.getInstance(GoodsParameterManager.class);
    CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();

    /**
     * 根据传入的查询参数获取满足条件的记录
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecords(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        //从bodyJson中获取传递的参数, 各参数字段含义 请参看 insertOrUpdate方法体中的注释
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         id	序号
         goods_gather_area_id	供应链商品聚集区编码
         class_lv1_id	商品一级分类编码
         class_lv2_id	商品小二分类编码
         goods_sales_grade_id	商品销量等级编码
         variety_id	品种编码
         aggregation_id	聚合商品编码
         sales_package_spec_type_id	可售量包规类型编码
         standard_safety_stock_days	标准安全库存天数
         temp_safety_stock_days	临期安全库存天数
         safety_stock_warn_days	安全库存预警天数
         spot_purchase_replenish_price_discount	现采补货价格折扣
         spot_purchase_stock_price_discount	现采囤货价格折扣
         admit_time_line	准入时间轴
         list_goods_id	挂牌商品编码
         list_goods_name	挂牌商品名
         list_goods_grade_id	挂牌商品等级编码
         list_goods_status	挂牌商品状态码
         sales_quantity_scheme_id	可售量方案编码
         list_goods_price_mode_id	挂牌商品计价方式
         list_goods_package_weight	挂牌商品外包装物重量
         list_goods_package_length	挂牌商品外包装长
         list_goods_package_width	挂牌商品外包装宽
         list_goods_package_height	挂牌商品外包装高
         list_goods_package_volume	挂牌商品外包装体积
         list_goods_package_size	挂牌商品外包装尺寸
         list_goods_specification_id	挂牌商品规格编码
         list_package_spec	挂牌商品挂牌包装规格
         list_goods_weight_package_spec	挂牌商品称重包装规格
         sales_package_spec	挂牌商品可售量包装规格
         supply_chain_goods_id	供应链商品编码
         supply_chain_goods_name	供应链商品名
         supply_chain_goods_grade_id	供应链商品等级编码
         supply_chain_goods_status	供应链商品状态码
         supply_chain_goods_settle_package_spec	供应链商品结算包规
         supply_chain_goods_algorithm_package_spec	供应链商品算法包规
         supply_chain_goods_weight_package_spec	供应链商品称重包规
         supply_chain_goods_package_weight	供应链商品包装物标准件重
         supply_chain_goods_price_mode_id	供应链商品计价方式
         supply_chain_goods_package_length	供应链商品外包装长
         supply_chain_goods_package_width	供应链商品外包装宽
         supply_chain_goods_package_height	供应链商品外包装高
         supply_chain_goods_package_volume	供应链商品外包体积
         supply_chain_goods_package_size	供应链商品外包装尺寸
         supply_chain_goods_specification_id	供应链商品规格编码
         is_delete	是否生效
         create_user	创建人
         update_user	更新人
         create_time	创建时间
         update_time	更新时间
         */

        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("goods_gather_area_id_list", "class_lv1_id_list", "class_lv2_id_list", "variety_id_list", "aggregation_id_list",
        "goods_sales_grade_id_list", "list_goods_status", "supply_chain_goods_status", "is_delete_list");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            logger.info("验证必须传递的字段: {}", must.toString());
            promise.complete(must);
            return promise.future();
        }
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }
        //供应链商品聚集区编码
        String goods_gather_area_id_list = bodyJSObj.getString("goods_gather_area_id_list");
        //商品一级分类编码
        String class_lv1_id_list = bodyJSObj.getString("class_lv1_id_list");
        String class_lv2_id_list = bodyJSObj.getString("class_lv2_id_list");
        String variety_id_list = bodyJSObj.getString("variety_id_list");
        String aggregation_id_list = bodyJSObj.getString("aggregation_id_list");
        String goods_sales_grade_id_list = bodyJSObj.getString("goods_sales_grade_id_list");
        String list_goods_status = bodyJSObj.getString("list_goods_status");
        String supply_chain_goods_status = bodyJSObj.getString("supply_chain_goods_status");
        String is_delete_list = bodyJSObj.getString("is_delete_list");
        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));

        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        // 传递给构建查询sql中where子句的JsonObject,
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("goods_gather_area_id", goods_gather_area_id_list);
        queryJSObj.put("class_lv1_id", class_lv1_id_list);
        queryJSObj.put("class_lv2_id", class_lv2_id_list);
        queryJSObj.put("variety_id", variety_id_list);
        queryJSObj.put("aggregation_id", aggregation_id_list);
        queryJSObj.put("goods_sales_grade_id", goods_sales_grade_id_list);
        queryJSObj.put("list_goods_status", list_goods_status);
        queryJSObj.put("supply_chain_goods_status", supply_chain_goods_status);
        queryJSObj.put("goods_parameter.is_delete", is_delete_list);
        /******
         *  获取每一个 goods_parameter 中多个code对应的name
         ******/
        //定义多个set 
        Set<String> goods_gather_area_id_list_set = new HashSet<String>();
        Set<String> class_lv1_id_list_set = new HashSet<String>();
        Set<String> class_lv2_id_list_set = new HashSet<String>();
        Set<String> variety_id_list_set = new HashSet<String>();
        Set<String> aggregation_id_list_set = new HashSet<String>();
        Set<String> goods_sales_grade_id_list_set = new HashSet<String>();
        Set<String> sales_package_spec_type_id_list_set = new HashSet<String>();
        Set<String> sales_quantity_scheme_id_list_set = new HashSet<String>();
        Set<String> list_goods_grade_id_list_set = new HashSet<String>();
        Set<String> list_goods_price_mode_id_list_set = new HashSet<String>();
        Set<String> list_goods_specification_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_grade_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_price_mode_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_specification_id_list_set = new HashSet<String>();
        Set<String> list_goods_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_id_list_set = new HashSet<String>();
        //每条记录对应子表的id
        Set<String> goods_parameter_list_goods_id_list_set = new HashSet<String>();
        Set<String> goods_parameter_supply_chain_goods_id_list_set = new HashSet<String>();

        this.commonRepository.getCon(client).onSuccess(conn -> {
            String sql = createSelectSql(queryJSObj, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, sql, "getGoodsParameterRecords")
            .onSuccess(resJSArray -> {
            	//如果有满足条件的数据
        		if(resJSArray.size() > 0) {
	            	//取出每条记录中含code字段的值放入对应的集合
	                for (int i = 0; i < resJSArray.size(); i++) {
	                    JsonObject jsonObj = resJSArray.getJsonObject(i);
	                    goods_gather_area_id_list_set.add(jsonObj.getString("goods_gather_area_id"));
	                    class_lv1_id_list_set.add(jsonObj.getString("class_lv1_id"));
	                    class_lv2_id_list_set.add(jsonObj.getString("class_lv2_id").toString());
	                    variety_id_list_set.add(jsonObj.getString("variety_id").toString());
	                    aggregation_id_list_set.add(jsonObj.getString("aggregation_id").toString());
	                    goods_sales_grade_id_list_set.add(jsonObj.getString("goods_sales_grade_id").toString());
	                    sales_package_spec_type_id_list_set.add(jsonObj.getString("sales_package_spec_type_id").toString());
	                    list_goods_grade_id_list_set.add(jsonObj.getString("list_goods_grade_id").toString());
	                    sales_quantity_scheme_id_list_set.add(jsonObj.getString("sales_quantity_scheme_id").toString());
	                    list_goods_price_mode_id_list_set.add(jsonObj.getString("list_goods_price_mode_id").toString());
	                    list_goods_specification_id_list_set.add(jsonObj.getString("list_goods_specification_id").toString());
	                    supply_chain_goods_grade_id_list_set.add(jsonObj.getString("supply_chain_goods_grade_id").toString());
	                    supply_chain_goods_price_mode_id_list_set.add(jsonObj.getString("supply_chain_goods_price_mode_id").toString());
	                    supply_chain_goods_specification_id_list_set.add(jsonObj.getString("supply_chain_goods_specification_id").toString());
	                    goods_parameter_list_goods_id_list_set.add(jsonObj.getString("goods_parameter_list_goods_id").toString());
	                    goods_parameter_supply_chain_goods_id_list_set.add(jsonObj.getString("goods_parameter_supply_chain_goods_id").toString());
	                    list_goods_id_list_set.add(jsonObj.getString("list_goods_id").toString());
	                    supply_chain_goods_id_list_set.add(jsonObj.getString("supply_chain_goods_grade_id").toString());
	                }
	
	                //获取满足条件的记录总数
	                String count_sql = createCountSql(queryJSObj);
	                Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_goods_parameter");
	                //获取供应链商品聚集区编码和名称
	                String goods_gather_area_sql = "select distinct id, goods_gather_area_name from goods_gather_area where is_delete = 0 and id = any(array[" +
	                String.join(",", goods_gather_area_id_list_set) + "]) ";
	                Future<JsonObject> getGoodsGatherAreaFuture = this.commonRepository.getCodeNamePairJsonObject(conn, goods_gather_area_sql, "get_goods_gather_area_name");
	                //获取商品一级分类编码和名称
	                String class_lv1_sql = "select distinct id,class_name from goods_class_lv1 where id = any(array[" +
	                String.join(",", class_lv1_id_list_set) + "]) and is_delete=0";
	                Future<JsonObject> getClassLv1Future = this.commonRepository.getCodeNamePairJsonObject(conn, class_lv1_sql, "get_class_lv1_name");
	                //获取小二分类编码和名称
	                String class_lv2_sql = "select id,class_name from goods_class_lv2 where id = any(array[" +
	                String.join(",", class_lv2_id_list_set) + "]) and is_delete=0";
	                Future<JsonObject> getClassLv2Future = this.commonRepository.getCodeNamePairJsonObject(conn, class_lv2_sql, "get_class_lv2_name");
	                //获取品种编码和名称
	                String variety_sql = "select id,class_name from goods_variety where id = any(array[" + String.join(",", variety_id_list_set) + "]) and is_delete=0";
	                Future<JsonObject> getVarietyFuture = this.commonRepository.getCodeNamePairJsonObject(conn, variety_sql, "get_variety_name");
	                //获取聚合商品编码和名称
	                String aggregation_sql = "select distinct id, aggregation_name from  aggregation_goods where id = any(array[" +
	                String.join(",", aggregation_id_list_set) + "]) ";
	                Future<JsonObject> getAggregationFuture = this.commonRepository.getCodeNamePairJsonObject(conn, aggregation_sql, "get_aggregation_name");
	                //商品销量等级编码和名称
	                String goods_sales_grade_sql = "select distinct id, grade_name from  goods_sales_grade where id = any(array[" +
	                String.join(",", goods_sales_grade_id_list_set) + "]) ";
	                Future<JsonObject> getGoodsSalesGradeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, goods_sales_grade_sql, "get_goods_sales_grade_name");
	                //可售量包规类型编码和名称
	                String sales_package_spec_sql = "select distinct id, type_name from  sales_package_spec_type where id = any(array[" +
	                String.join(",", sales_package_spec_type_id_list_set) + "]) ";
	                Future<JsonObject> getSalesPackageSpecFuture = this.commonRepository.getCodeNamePairJsonObject(conn, sales_package_spec_sql, "get_sales_package_spec_name");
	                //supplyChain商品等级编码和名称
	                String list_goods_grade_sql = "select distinct id, list_goods_grade_name from  goods_pool_loan_relation where id = any(array[" +
	                String.join(",", list_goods_grade_id_list_set) + "]) ";
	                Future<JsonObject> getListGoodsGradeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, list_goods_grade_sql, "get_list_goods_grade_name");
	                //挂牌商品定价方式编码和名称 
	                String list_goods_price_mode_sql = "select distinct id, price_mode_name from  goods_price_mode where id = any(array[" +
	                String.join(",", list_goods_price_mode_id_list_set) + "]) ";
	                Future<JsonObject> getListGoodsPriceModeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, list_goods_price_mode_sql, "get_list_goods_price_mode_name");
	                //挂牌商品规格编码和名称 
	                String list_goods_specification_sql = "select distinct id, specification_name from  goods_specification where id = any(array[" +
	                String.join(",", list_goods_specification_id_list_set) + "]) ";
	                Future<JsonObject> getListGoodsSpecificationFuture = this.commonRepository.getCodeNamePairJsonObject(conn, list_goods_specification_sql, "get_list_goods_specification_name");
	                //供应链商品等级编码和名称
	                String supply_chain_goods_grade_sql = "select distinct id, grade_name from  goods_grade where id = any(array[" +
	                String.join(",", supply_chain_goods_grade_id_list_set) + "]) ";
	                Future<JsonObject> getSupplyChainGoodsGradeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, supply_chain_goods_grade_sql, "get_supply_chain_goods_grade_name");
	                //供应链商品定价方式编码和名称 
	                String supply_chain_goods_price_mode_sql = "select distinct id, price_mode_name from goods_price_mode where is_delete=0 and id = any(array[" +
	                String.join(",", supply_chain_goods_price_mode_id_list_set) + "]) ";
	                Future<JsonObject> getSupplyChainGoodsPriceModeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, supply_chain_goods_price_mode_sql, "get_supply_chain_goods_price_mode_name");
	                //供应链商品规格编码和名称 
	                String supply_chain_goods_specification_sql = "select distinct id, specification_name from  goods_specification where is_delete=0 and id = any(array[" +
	                String.join(",", supply_chain_goods_specification_id_list_set) + "]) ";
	                Future<JsonObject> getSupplyChainGoodsSpecificationFuture = this.commonRepository.getCodeNamePairJsonObject(conn, supply_chain_goods_specification_sql, "get_supply_chain_goods_specification__name");
	                //可售量方案编码和名称
	                String sales_quantity_scheme_sql = "select distinct id, sales_quantity_scheme_name from  goods_pool_loan_relation where id = any(array[" +
	                String.join(",", sales_quantity_scheme_id_list_set) + "]) ";
	                Future<JsonObject> getSalesQuantitySchemeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, sales_quantity_scheme_sql, "get_sales_quantity_scheme_name");
	                //挂牌商品名编码和名称
	                String list_goods_sql = "select distinct id, list_goods_name from list_goods where id = any(array[" +
	                String.join(",", list_goods_id_list_set) + "]) ";
	                Future<JsonObject> getListGoodsFuture = this.commonRepository.getCodeNamePairJsonObject(conn, list_goods_sql, "get_sales_quantity_scheme_name");
	                //供应链商品编码和名称
	                String supply_chain_goods_sql = "select distinct id, supply_chain_goods_name from supply_chain_goods where id = any(array[" +
	                String.join(",", supply_chain_goods_id_list_set) + "]) ";
	                Future<JsonObject> getSupplyChainGoodsFuture = this.commonRepository.getCodeNamePairJsonObject(conn, supply_chain_goods_sql, "get_sales_quantity_scheme_name");
	
	                //获取挂牌商品分表goods_parameter_list_goods数据
	                String goods_parameter_list_goods_sql = "select distinct id, list_goods_status,list_package_spec,list_goods_weight_package_spec,sales_package_spec,list_goods_package_weight,list_goods_package_length,list_goods_package_width," +
	                "list_goods_package_height,list_goods_package_volume,list_goods_package_size from goods_parameter_list_goods where is_delete = 0 and id = any(array[" +
	                String.join(",", goods_parameter_list_goods_id_list_set) + "]) ";
	                Future<JsonObject> getGoodsParameterListGoodsFuture = this.commonRepository.queryJsonArrayManyToMany(conn, goods_parameter_list_goods_sql, "get_goods_gather_area_name");
	                //获取挂牌商品分表goods_parameter_supply_chain_goods数据
	                String goods_parameter_supply_chain_goods_sql = "select distinct id, supply_chain_goods_status,supply_chain_goods_algorithm_package_spec,supply_chain_goods_weight_package_spec,supply_chain_goods_package_weight,supply_chain_goods_package_length,supply_chain_goods_package_width," +
	                "supply_chain_goods_package_height,supply_chain_goods_package_volume,supply_chain_goods_package_size from goods_parameter_supply_chain_goods where is_delete = 0 and id = any(array[" +
	                String.join(",", goods_parameter_supply_chain_goods_id_list_set) + "]) ";
	                Future<JsonObject> getGoodsParameterSupplyChainGoodsFuture = this.commonRepository.queryJsonArrayManyToMany(conn, goods_parameter_supply_chain_goods_sql, "get_goods_gather_area_name");
	
	                CompositeFuture.join(Arrays.asList(getTotalFuture, getGoodsGatherAreaFuture, getClassLv1Future, getClassLv2Future, getVarietyFuture, getAggregationFuture, getGoodsSalesGradeFuture,
	                getSalesPackageSpecFuture, getListGoodsGradeFuture, getListGoodsPriceModeFuture, getListGoodsSpecificationFuture, getSupplyChainGoodsGradeFuture, getSupplyChainGoodsPriceModeFuture,
	                getSupplyChainGoodsSpecificationFuture, getSalesQuantitySchemeFuture, getListGoodsFuture, getSupplyChainGoodsFuture, getGoodsParameterListGoodsFuture, getGoodsParameterSupplyChainGoodsFuture)).onComplete(ar2 -> {
	                    if (ar2.succeeded()) {
	                        JsonObject goods_gather_area_info = new JsonObject();
	                        JsonObject class_lv1_info = new JsonObject();
	                        JsonObject class_lv2_info = new JsonObject();
	                        JsonObject variety_id_info = new JsonObject();
	                        JsonObject aggregation_info = new JsonObject();
	                        JsonObject goods_sales_grade_info = new JsonObject();
	                        JsonObject sales_package_spec_type_info = new JsonObject();
	                        JsonObject list_goods_grade_info = new JsonObject();
	                        JsonObject list_goods_price_mode_info = new JsonObject();
	                        JsonObject list_goods_specification_info = new JsonObject();
	                        JsonObject supply_chain_goods_grade_info = new JsonObject();
	                        JsonObject supply_chain_goods_price_mode_info = new JsonObject();
	                        JsonObject supply_chain_goods_specification_info = new JsonObject();
	                        JsonObject sales_quantity_scheme_info = new JsonObject();
	                        JsonObject list_goods_info = new JsonObject();
	                        JsonObject supply_chain_goods_info = new JsonObject();
	                        JsonObject goods_parameter_list_goods_info = new JsonObject();
	                        JsonObject goods_parameter_supply_chain_goods_info = new JsonObject();
	
	                        int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
	                        goods_gather_area_info = (JsonObject) ar2.result().list().get(1);
	                        class_lv1_info = (JsonObject) ar2.result().list().get(2);
	                        class_lv2_info = (JsonObject) ar2.result().list().get(3);
	                        variety_id_info = (JsonObject) ar2.result().list().get(4);
	                        aggregation_info = (JsonObject) ar2.result().list().get(5);
	                        goods_sales_grade_info = (JsonObject) ar2.result().list().get(6);
	                        sales_package_spec_type_info = (JsonObject) ar2.result().list().get(7);
	                        list_goods_grade_info = (JsonObject) ar2.result().list().get(8);
	                        list_goods_price_mode_info = (JsonObject) ar2.result().list().get(9);
	                        list_goods_specification_info = (JsonObject) ar2.result().list().get(10);
	                        supply_chain_goods_grade_info = (JsonObject) ar2.result().list().get(11);
	                        supply_chain_goods_price_mode_info = (JsonObject) ar2.result().list().get(12);
	                        supply_chain_goods_specification_info = (JsonObject) ar2.result().list().get(13);
	                        sales_quantity_scheme_info = (JsonObject) ar2.result().list().get(14);
	                        list_goods_info = (JsonObject) ar2.result().list().get(15);
	                        supply_chain_goods_info = (JsonObject) ar2.result().list().get(16);
	                        goods_parameter_list_goods_info = (JsonObject) ar2.result().list().get(17);
	                        goods_parameter_supply_chain_goods_info = (JsonObject) ar2.result().list().get(18);
	
	                        // 从配置文件中读取
	                        JsonObject is_delete_info = config.getJsonObject("DELETE_INFO");
	                        JsonObject goods_status_info = config.getJsonObject("GOODS_STATUS_INFO");
	                        // 最后组装含有name的Json对象和数组
	                        JsonArray recordsJSArray = new JsonArray();
	                        for (int i = 0; i < resJSArray.size(); i++) {
	                            JsonObject record_obj = resJSArray.getJsonObject(i);
	                            String goods_gather_area_id = record_obj.getString("goods_gather_area_id");
	                            record_obj.put("goods_gather_area_name", goods_gather_area_info.getString(goods_gather_area_id));
	                            String class_lv1_id = record_obj.getString("class_lv1_id");
	                            record_obj.put("class_lv1_name", class_lv1_info.getString(class_lv1_id));
	                            String class_lv2_id = record_obj.getString("class_lv2_id");
	                            record_obj.put("class_lv2_name", class_lv2_info.getString(class_lv2_id));
	                            String variety_id = record_obj.getString("variety_id");
	                            record_obj.put("variety_name", variety_id_info.getString(variety_id));
	                            record_obj.put("aggregation_name", aggregation_info.getString(record_obj.getString("aggregation_id")));
	                            record_obj.put("goods_sales_grade_name", goods_sales_grade_info.getString(record_obj.getString("goods_sales_grade_id")));
	                            record_obj.put("sales_package_spec_type_name", sales_package_spec_type_info.getString(record_obj.getString("sales_package_spec_type_id")));
	                            record_obj.put("list_goods_grade_name", list_goods_grade_info.getString(record_obj.getString("list_goods_grade_id")));
	                            record_obj.put("list_goods_price_mode_name", list_goods_price_mode_info.getString(record_obj.getString("list_goods_price_mode_id")));
	                            record_obj.put("list_goods_specification_name", list_goods_specification_info.getString(record_obj.getString("list_goods_specification_id")));
	                            record_obj.put("supply_chain_goods_grade_name", supply_chain_goods_grade_info.getString(record_obj.getString("supply_chain_goods_grade_id")));
	                            record_obj.put("supply_chain_goods_price_mode_name", supply_chain_goods_price_mode_info.getString(record_obj.getString("supply_chain_goods_price_mode_id")));
	                            record_obj.put("supply_chain_goods_specification_name", supply_chain_goods_specification_info.getString(record_obj.getString("supply_chain_goods_specification_id")));
	                            record_obj.put("sales_quantity_scheme_name", sales_quantity_scheme_info.getString(record_obj.getString("sales_quantity_scheme_id")));
	                            record_obj.put("list_goods_name", list_goods_info.getString(record_obj.getString("list_goods_id")));
	                            record_obj.put("supply_chain_goods_name", supply_chain_goods_info.getString(record_obj.getString("supply_chain_goods_id")));
	                            //获取挂牌商品子表的数据
	                            JsonObject jsonObject = goods_parameter_list_goods_info.getJsonObject(record_obj.getString("goods_parameter_list_goods_id"));
	                            record_obj.put("list_package_spec", jsonObject.getString("list_package_spec"));
	                            record_obj.put("list_goods_weight_package_spec", jsonObject.getString("list_goods_weight_package_spec"));
	                            record_obj.put("sales_package_spec", jsonObject.getString("sales_package_spec"));
	                            record_obj.put("list_goods_package_weight", jsonObject.getString("list_goods_package_weight"));
	                            record_obj.put("list_goods_package_length", jsonObject.getString("list_goods_package_length"));
	                            record_obj.put("list_goods_package_width", jsonObject.getString("list_goods_package_width"));
	                            record_obj.put("list_goods_package_height", jsonObject.getString("list_goods_package_height"));
	                            record_obj.put("list_goods_package_volume", jsonObject.getString("list_goods_package_volume"));
	                            record_obj.put("list_goods_package_size", jsonObject.getString("list_goods_package_size"));
	                            //获取供应链商品子表的数据
	                            jsonObject = goods_parameter_supply_chain_goods_info.getJsonObject(record_obj.getString("goods_parameter_supply_chain_goods_id"));
	                            record_obj.put("supply_chain_goods_settle_package_spec", jsonObject.getString("supply_chain_goods_settle_package_spec"));
	                            record_obj.put("supply_chain_goods_algorithm_package_spec", jsonObject.getString("supply_chain_goods_algorithm_package_spec"));
	                            record_obj.put("supply_chain_goods_weight_package_spec", jsonObject.getString("supply_chain_goods_weight_package_spec"));
	                            record_obj.put("supply_chain_goods_package_weight", jsonObject.getString("supply_chain_goods_package_weight"));
	                            record_obj.put("supply_chain_goods_package_length", jsonObject.getString("supply_chain_goods_package_length"));
	                            record_obj.put("supply_chain_goods_package_width", jsonObject.getString("supply_chain_goods_package_width"));
	                            record_obj.put("supply_chain_goods_package_height", jsonObject.getString("supply_chain_goods_package_height"));
	                            record_obj.put("supply_chain_goods_package_volume", jsonObject.getString("supply_chain_goods_package_volume"));
	                            record_obj.put("supply_chain_goods_package_size", jsonObject.getString("supply_chain_goods_package_size"));
	                            record_obj.put("is_delete_name", is_delete_info.getString(record_obj.getString("is_delete")));
	                            recordsJSArray.add(record_obj);
	                        }
	                        JsonObject dataJSObj = new JsonObject();
	                        dataJSObj.put("total", total_records).put("records", recordsJSArray);
	                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
	                        promise.complete(responseJSObj);
	                    } else {
	                        logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
	                        promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getGoodsParameterRecords", "获取商品参数失败"));
	                    }
	                });
        		} else { //如果没有满足条件的数据
	    			JsonObject dataJSObj = new JsonObject();
	                dataJSObj.put("total", 0).put("records", resJSArray);
	                responseJSObj.put("code", 200).put("msg", "没有满足条件的数据").put("data", dataJSObj);   
	                promise.complete(responseJSObj);
	    		} 
            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getGoodsParameterRecords", "获取商品参数失败")));
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsParameterRecords", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句  数据表 goods_parameter 字段注释
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSql(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("goods_parameter.id"), new CustomSql("goods_gather_area_id"), new CustomSql("class_lv1_id"),
        new CustomSql("class_lv2_id"), new CustomSql("goods_sales_grade_id"), new CustomSql("variety_id"), new CustomSql("aggregation_id"), new CustomSql("sales_package_spec_type_id"),
        new CustomSql("standard_safety_stock_days"), new CustomSql("temp_safety_stock_days"), new CustomSql("safety_stock_warn_days"), new CustomSql("spot_purchase_replenish_price_discount"), 
        new CustomSql("spot_purchase_stock_price_discount"),  new CustomSql("admit_time_line"), new CustomSql("goods_parameter_list_goods_id"), new CustomSql("list_goods_id"), 
        new CustomSql("list_goods_grade_id"), new CustomSql("sales_quantity_scheme_id"),  new CustomSql("list_goods_price_mode_id"), new CustomSql("list_goods_specification_id"),
        new CustomSql("goods_parameter_supply_chain_goods_id"), new CustomSql("supply_chain_goods_id"), new CustomSql("supply_chain_goods_grade_id"), new CustomSql("supply_chain_goods_price_mode_id"),
        new CustomSql("supply_chain_goods_specification_id"), new CustomSql("goods_parameter.is_delete"), new CustomSql("goods_parameter.create_user"), new CustomSql("goods_parameter.update_user"), 
        new CustomSql("goods_parameter.create_time"), new CustomSql("goods_parameter.update_time")
        );
        // add left_outer join two tables
        sq.addCustomJoin(SelectQuery.JoinType.LEFT_OUTER,
	                new CustomSql("goods_parameter"), new CustomSql("goods_parameter_list_goods"),
	                BinaryCondition.equalTo(
	                  new CustomSql("goods_parameter.goods_parameter_list_goods_id"), new CustomSql("goods_parameter_list_goods.id")));
	   	 // add left_outer join two tables 
	   	sq.addCustomJoin(SelectQuery.JoinType.LEFT_OUTER,
                new CustomSql("goods_parameter"), new CustomSql("goods_parameter_supply_chain_goods"),
                BinaryCondition.equalTo(
                  new CustomSql("goods_parameter.goods_parameter_supply_chain_goods_id"), new CustomSql("goods_parameter_supply_chain_goods.id")));
	   	this.commonRepository.processQueryJsonObject(sq, queryJS);  
        String sql = sq.validate() + " order by update_time desc limit " + query_limit + " offset " + query_offset;
        logger.info(sql);
        return sql;
    }
    /**
     * 查询记录总数，多个表连接
     * @param queryJS
     * @return
     */
    public String createCountSql(JsonObject queryJS ) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql(" count(*) as total "));
        // add left_outer join two tables
        sq.addCustomJoin(SelectQuery.JoinType.LEFT_OUTER,
	                new CustomSql("goods_parameter"), new CustomSql("goods_parameter_list_goods"),
	                BinaryCondition.equalTo(
	                  new CustomSql("goods_parameter.goods_parameter_list_goods_id"), new CustomSql("goods_parameter_list_goods.id")));
	   	 // add left_outer join two tables 
	   	sq.addCustomJoin(SelectQuery.JoinType.LEFT_OUTER,
                new CustomSql("goods_parameter"), new CustomSql("goods_parameter_supply_chain_goods"),
                BinaryCondition.equalTo(
                  new CustomSql("goods_parameter.goods_parameter_supply_chain_goods_id"), new CustomSql("goods_parameter_supply_chain_goods.id")));
	   	this.commonRepository.processQueryJsonObject(sq, queryJS);  
        return sq.validate().toString();
    }


    /**
     * 该方法实现接收web前端通过body传入的参数，调用通用类CommonRepositoryImpl的方法实现数据的增、删、改 。
     * 通过含参数的Json对象bodyJSObj，通过method字段获取操作类型，分别是insert，update, delete, admit, block。
     * 1) 操作类型为 insert，首先查询传入的商品参数名是否存在，如果不存在就新增数据，存在就更新数据。
     * 2) 操作类型为 update，根据id更新记录
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @param user      操作用户
     * @return 操作的结果
     * @throws Exception
     */
    public Future<JsonObject> insertOrUpdate(PgPool client, JsonObject config, JsonObject bodyJSObj, User user) {
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         id	序号
         goods_gather_area_id	供应链商品聚集区编码
         class_lv1_id	商品一级分类编码
         class_lv2_id	商品小二分类编码
         goods_sales_grade_id	商品销量等级编码
         variety_id	品种编码
         aggregation_id	聚合商品编码
         sales_package_spec_type_id	可售量包规类型编码
         standard_safety_stock_days	标准安全库存天数
         temp_safety_stock_days	临期安全库存天数
         safety_stock_warn_days	安全库存预警天数
         spot_purchase_replenish_price_discount	现采补货价格折扣
         spot_purchase_stock_price_discount	现采囤货价格折扣
         admit_time_line	准入时间轴
         list_goods_id	挂牌商品编码
         list_goods_grade_id	挂牌商品等级编码
         list_goods_status	挂牌商品状态码
         sales_quantity_scheme_id	可售量方案编码
         list_goods_price_mode_id	挂牌商品计价方式
         list_goods_package_weight	挂牌商品外包装物重量
         list_goods_package_length	挂牌商品外包装长
         list_goods_package_width	挂牌商品外包装宽
         list_goods_package_height	挂牌商品外包装高
         list_goods_package_volume	挂牌商品外包装体积
         list_goods_package_size	挂牌商品外包装尺寸
         list_goods_specification_id	挂牌商品规格编码
         list_package_spec	挂牌商品挂牌包装规格
         list_goods_weight_package_spec	挂牌商品称重包装规格
         sales_package_spec	挂牌商品可售量包装规格
         supply_chain_goods_id	供应链商品编码
         supply_chain_goods_name	供应链商品名
         supply_chain_goods_grade_id	供应链商品等级编码
         supply_chain_goods_status	供应链商品状态码
         supply_chain_goods_settle_package_spec	供应链商品结算包规
         supply_chain_goods_algorithm_package_spec	供应链商品算法包规
         supply_chain_goods_weight_package_spec	供应链商品称重包规
         supply_chain_goods_package_weight	供应链商品包装物标准件重
         supply_chain_goods_price_mode_id	供应链商品计价方式
         supply_chain_goods_package_length	供应链商品外包装长
         supply_chain_goods_package_width	供应链商品外包装宽
         supply_chain_goods_package_height	供应链商品外包装高
         supply_chain_goods_package_volume	供应链商品外包体积
         supply_chain_goods_package_size	供应链商品外包装尺寸
         supply_chain_goods_specification_id	供应链商品规格编码
         is_delete	是否生效
         create_user	创建人
         update_user	更新人
         create_time	创建时间
         update_time	更新时间
         */
        Promise<JsonObject> promise = Promise.promise();
        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId"); 
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("method");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }
        // 验证数值字段
        List<String> numeric_fields = Arrays.asList( "goods_gather_area_id", "class_lv1_id", "class_lv2_id", "variety_id", "aggregation_id", "goods_sales_grade_id", "sales_package_spec_type_id", "list_goods_id", "list_goods_grade_id", "sales_quantity_scheme_id",
                "list_goods_price_mode_id", "list_goods_specification_id", "supply_chain_goods_id", "supply_chain_goods_grade_id", "supply_chain_goods_price_mode_id", "supply_chain_goods_specification_id",
                "temp_safety_stock_days", "safety_stock_warn_days", "spot_purchase_replenish_price_discount", "spot_purchase_stock_price_discount", "list_goods_package_weight", "list_goods_package_length", "list_goods_package_width", "list_goods_package_height",
        "supply_chain_goods_settle_package_spec", "supply_chain_goods_algorithm_package_spec", "supply_chain_goods_weight_package_spec", "supply_chain_goods_package_weight", "supply_chain_goods_package_length", "supply_chain_goods_package_width", "supply_chain_goods_package_height", "id");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }
        // 获取操作类型字段的值（必须字段）
        String method = bodyJSObj.getString("method");
        Long goods_gather_area_id = bodyJSObj.getLong("goods_gather_area_id");
        Long class_lv1_id = bodyJSObj.getLong("class_lv1_id");
        Long class_lv2_id = bodyJSObj.getLong("class_lv2_id");
        Long variety_id = bodyJSObj.getLong("variety_id");
        Long aggregation_id = bodyJSObj.getLong("aggregation_id");
        Long goods_sales_grade_id = bodyJSObj.getLong("goods_sales_grade_id");
        Long sales_package_spec_type_id = bodyJSObj.getLong("sales_package_spec_type_id");
        double temp_safety_stock_days = Double.parseDouble(bodyJSObj.getString("temp_safety_stock_days"));
        double safety_stock_warn_days = Double.parseDouble(bodyJSObj.getString("safety_stock_warn_days"));
        double spot_purchase_replenish_price_discount = Double.parseDouble(bodyJSObj.getString("spot_purchase_replenish_price_discount"));
        double spot_purchase_stock_price_discount = Double.parseDouble(bodyJSObj.getString("spot_purchase_stock_price_discount"));
        //挂牌商品
        Long list_goods_id = bodyJSObj.getLong("list_goods_id");
        Long list_goods_grade_id = bodyJSObj.getLong("list_goods_grade_id");
        Long sales_quantity_scheme_id = bodyJSObj.getLong("sales_quantity_scheme_id");
        Long list_goods_price_mode_id = bodyJSObj.getLong("list_goods_price_mode_id");
        double list_goods_package_weight = Double.parseDouble(bodyJSObj.getString("list_goods_package_weight"));
        double list_goods_package_length = Double.parseDouble(bodyJSObj.getString("list_goods_package_length"));
        double list_goods_package_width = Double.parseDouble(bodyJSObj.getString("list_goods_package_width"));
        double list_goods_package_height = Double.parseDouble(bodyJSObj.getString("list_goods_package_height"));
        double list_goods_package_volume = list_goods_package_length * list_goods_package_width * list_goods_package_height;
        String list_goods_package_size = list_goods_package_length + "*" + list_goods_package_width + "*" + list_goods_package_height;
        Long list_goods_specification_id = bodyJSObj.getLong("list_goods_specification_id");
        //供应链商品
        Long supply_chain_goods_id = bodyJSObj.getLong("supply_chain_goods_id");
        Long supply_chain_goods_grade_id = bodyJSObj.getLong("supply_chain_goods_grade_id");
        //结算包规
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        double supply_chain_goods_algorithm_package_spec = Double.parseDouble(bodyJSObj.getString("supply_chain_goods_algorithm_package_spec"));
        String supply_chain_goods_weight_package_spec = bodyJSObj.getString("supply_chain_goods_weight_package_spec");
        double supply_chain_goods_package_weight = Double.parseDouble(bodyJSObj.getString("supply_chain_goods_package_weight"));
        Long supply_chain_goods_price_mode_id = bodyJSObj.getLong("supply_chain_goods_price_mode_id");
        double supply_chain_goods_package_length = Double.parseDouble(bodyJSObj.getString("supply_chain_goods_package_length"));
        double supply_chain_goods_package_width = Double.parseDouble(bodyJSObj.getString("supply_chain_goods_package_width"));
        double supply_chain_goods_package_height = Double.parseDouble(bodyJSObj.getString("supply_chain_goods_package_height"));
        double supply_chain_goods_package_volume = supply_chain_goods_package_length * supply_chain_goods_package_width * supply_chain_goods_package_height;
        String supply_chain_goods_package_size = bodyJSObj.getString("supply_chain_goods_package_length").concat("*").
        concat(bodyJSObj.getString("supply_chain_goods_package_length"))
        .concat("*").concat(bodyJSObj.getString("supply_chain_goods_package_height"));
        Long supply_chain_goods_specification_id = bodyJSObj.getLong("supply_chain_goods_specification_id");

        // 获取bodyJSObj参数对象中字段的值
        long id = Long.valueOf(bodyJSObj.getString("id"));

        this.commonRepository.getCon(client).onSuccess(conn -> {
            /**
             * 校验多个code是否合法
             */
            //校验供应链商品聚集区code
            String goods_gather_area_id_sql = "select id, goods_gather_area_name from goods_gather_area where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> goodsGatherAreaCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, goods_gather_area_id_sql, Tuple.of(goods_gather_area_id), "check_code_before_insert_goods_parameter", "应链商品聚集区code 操作失败");
            //校验商品一级分类code
            String class_lv1_id_sql = "select id, class_name from goods_class_lv1 where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> classLv1CodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, class_lv1_id_sql, Tuple.of(class_lv1_id), "check_code_before_insert_goods_parameter", "商品一级分类code 操作失败");
            //校验商品小二分类code
            String class_lv2_id_sql = "select id, class_name from goods_class_lv2 where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> classLv2CodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, class_lv2_id_sql, Tuple.of(class_lv2_id), "check_code_before_insert_goods_parameter", "商品小二分类code 操作失败");
            //校验商品品种code
            String variety_id_sql = "select id, class_name from goods_variety where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> varietyCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, variety_id_sql, Tuple.of(variety_id), "check_code_before_insert_goods_parameter", "商品品种code 操作失败");
            //校验聚合商品code
            String aggregation_id_sql = "select  id, aggregation_name from aggregation_goods where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> aggregationCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, aggregation_id_sql, Tuple.of(aggregation_id), "check_code_before_insert_goods_parameter", "商品聚合商品code 操作失败");
            //校验商品可售量等级code
            String goods_sales_grade_id_sql = "select id,grade_name from goods_sales_grade where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> gradeCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, goods_sales_grade_id_sql, Tuple.of(goods_sales_grade_id), "check_code_before_insert_goods_parameter", "商品商品可售量等级code 操作失败");
            //校验可售量包规类型code
            String sales_package_spec_type_id_sql = "select id,type_name from sales_package_spec_type where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> salesPackageSpecTypeCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, sales_package_spec_type_id_sql, Tuple.of(sales_package_spec_type_id), "check_code_before_insert_goods_parameter", "可售量包规类型code 操作失败");
            //校验挂牌商品code
            String list_goods_id_sql = "select id, list_goods_name from list_goods where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> listGoodsCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, list_goods_id_sql, Tuple.of(list_goods_id), "check_code_before_insert_goods_parameter", "挂牌商品code 操作失败");
            //校验挂牌商品等级code 
            String list_goods_grade_id_ql = "select id, list_goods_grade_name from goods_pool_loan_relation where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> listGoodsGradeCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, list_goods_grade_id_ql, Tuple.of(list_goods_grade_id), "check_code_before_insert_goods_parameter", "挂牌商品等级code 操作失败");
            //校验可售量方案code   
            String sales_quantity_scheme_id_sql = "select id, sales_quantity_scheme_name from goods_pool_loan_relation where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> salesQuantitySchemeCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, sales_quantity_scheme_id_sql, Tuple.of(sales_quantity_scheme_id), "check_code_before_insert_goods_parameter", "可售量方案code 操作失败");
            //校验商品计价方式code  
            String list_goods_price_mode_id_sql = "select id, price_mode_name from goods_price_mode where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> listGoodsPriceModeCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, list_goods_price_mode_id_sql, Tuple.of(list_goods_price_mode_id), "check_code_before_insert_goods_parameter", "挂牌商品计价方式code 操作失败");
            //校验商品规格code  
            String list_goods_specification_id_sql = "select id, specification_name from goods_specification where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> listGoodsSpecificationCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, list_goods_specification_id_sql, Tuple.of(list_goods_specification_id), "check_code_before_insert_goods_parameter", "商品规格code   操作失败");

            //校验供应链商品code
            String supply_chain_goods_id_sql = "select id, supply_chain_goods_name from supply_chain_goods where id = $1  and is_delete = 0 limit 1";
            Future<JsonObject> supplyChainGoodsCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, supply_chain_goods_id_sql, Tuple.of(supply_chain_goods_id), "check_code_before_insert_goods_parameter", "供应链商品code 操作失败");
            //校验供应链商品等级code 
            String supply_chain_goods_grade_id_ql = "select id, supply_chain_goods_grade_name from goods_pool_loan_relation where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> supplyChainGoodsGradeCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, supply_chain_goods_grade_id_ql, Tuple.of(supply_chain_goods_grade_id), "check_code_before_insert_goods_parameter", "供应链商品等级code 操作失败");
            //校验商品计价方式code  
            String supply_chain_goods_price_mode_id_sql = "select id, price_mode_name from goods_price_mode where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> supplyChainGoodsPriceModeCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, supply_chain_goods_price_mode_id_sql, Tuple.of(supply_chain_goods_price_mode_id), "check_code_before_insert_goods_parameter", "供应链商品定价方式code 操作失败");
            //校验商品规格code  
            String supply_chain_goods_specification_id_sql = "select id, specification_name from goods_specification where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> supplyChainGoodsSpecificationCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, supply_chain_goods_specification_id_sql, Tuple.of(supply_chain_goods_specification_id), "check_code_before_insert_goods_parameter", "供应链商品规格code 操作失败");

            //从商品池借货关系表中读取的字段
            String get_value_from_goods_pool_sql = "select list_goods_status, supply_chain_goods_status, standard_safety_stock_days,  list_package_spec, sales_package_spec from goods_pool_loan_relation where list_goods_id = $1 and is_delete = 0 order by update_time desc limit 1";
            Future<JsonObject> getValueFromGoodsPoolFuture = this.commonRepository.queryOneJsonObject(conn, get_value_from_goods_pool_sql, Tuple.of(list_goods_id), "check_code_before_insert_goods_parameter", "商品池借货关系表中读取字段 操作失败");
            //从商品名系统录入参数在线管控表读取得字段
            String Id_by_code_sql = "select id,goods_parameter_list_goods_id,goods_parameter_supply_chain_goods_id from goods_parameter where aggregation_id = $1 and supply_chain_goods_id = $2 ";
            Future<JsonObject> getIdByCodeFuture = this.commonRepository.queryOneJsonObject(conn, Id_by_code_sql, Tuple.of(aggregation_id, supply_chain_goods_id), "insert_goods_parameter", "获取id 操作失败");

            if (method.equals("insert")) {
                String goods_parameter_by_code_sql = "select count(*) as total from goods_parameter where goods_gather_area_id = $1 and aggregation_id = $2 and supply_chain_goods_id = $3 and list_goods_id = $4 and supply_chain_goods_settle_package_spec = $5 ";
                Future<Integer> getGoodsParameterByCodeFuture = this.commonRepository.getRecordsCount(conn, goods_parameter_by_code_sql, Tuple.of(goods_gather_area_id, aggregation_id, supply_chain_goods_id, list_goods_id, supply_chain_goods_settle_package_spec), "insert_goods_parameter");
                // 并行执行多个future进行编码校验
                CompositeFuture.join(Arrays.asList(getGoodsParameterByCodeFuture, getValueFromGoodsPoolFuture, getIdByCodeFuture, goodsGatherAreaCodeFuture, classLv1CodeFuture, classLv2CodeFuture, varietyCodeFuture, aggregationCodeFuture, gradeCodeFuture, salesPackageSpecTypeCodeFuture, listGoodsCodeFuture,
                listGoodsGradeCodeFuture, salesQuantitySchemeCodeFuture, listGoodsPriceModeCodeFuture, listGoodsSpecificationCodeFuture, supplyChainGoodsCodeFuture, supplyChainGoodsGradeCodeFuture, supplyChainGoodsPriceModeCodeFuture, supplyChainGoodsSpecificationCodeFuture)).onComplete(ar -> {
                    if (ar.succeeded()) {
                        int total_records = Integer.valueOf(ar.result().list().get(0).toString());
                        JsonObject goods_pool_value = (JsonObject) ar.result().list().get(1);
                        //获取list_goods_status的值
                        int list_goods_status;
                        if (goods_pool_value.getValue("list_goods_status") != null && !goods_pool_value.getValue("list_goods_status").equals(""))
                            list_goods_status = Integer.valueOf(goods_pool_value.getString("list_goods_status"));
                        else {
                            list_goods_status = 0;
                        }
                        //获取supply_chain_goods_status的值
                        int supply_chain_goods_status;
                        if (goods_pool_value.getValue("supply_chain_goods_status") != null && !goods_pool_value.getValue("supply_chain_goods_status").equals(""))
                            supply_chain_goods_status = Integer.valueOf(goods_pool_value.getString("supply_chain_goods_status"));
                        else {
                            supply_chain_goods_status = 0;
                        }
                        //获取standard_safety_stock_days的值
                        double standard_safety_stock_days;
                        if (goods_pool_value.getValue("standard_safety_stock_days") != null && !goods_pool_value.getValue("standard_safety_stock_days").equals(""))
                            standard_safety_stock_days = Double.valueOf(goods_pool_value.getString("standard_safety_stock_days"));
                        else {
                            standard_safety_stock_days = 0;
                        }
                        //获取list_package_spec的值
                        double list_package_spec;
                        if (goods_pool_value.getValue("list_package_spec") != null && !goods_pool_value.getValue("list_package_spec").equals(""))
                            list_package_spec = Double.valueOf(goods_pool_value.getString("list_package_spec"));
                        else {
                            list_package_spec = 0;
                        }
                        //获取list_package_spec的值
                        double sales_package_spec;
                        if (goods_pool_value.getValue("sales_package_spec") != null && !goods_pool_value.getValue("sales_package_spec").equals(""))
                            sales_package_spec = Double.valueOf(goods_pool_value.getString("sales_package_spec"));
                        else {
                            sales_package_spec = 0;
                        }
                        //计算 list_goods_weight_package_spec
                        double list_goods_weight_package_spec = sales_package_spec + list_goods_package_weight;
                        String admit_time_line = "";

                        goods_pool_value = (JsonObject) ar.result().list().get(2);
                        long goods_parameter_list_goods_id;
                        if (goods_pool_value.getValue("goods_parameter_list_goods_id") != null && !goods_pool_value.getValue("goods_parameter_list_goods_id").equals(""))
                            goods_parameter_list_goods_id = Long.valueOf(goods_pool_value.getString("goods_parameter_list_goods_id"));
                        else {
                            goods_parameter_list_goods_id = 0;
                        }
                        long goods_parameter_supply_chain_goods_id;
                        if (goods_pool_value.getValue("goods_parameter_supply_chain_goods_id") != null && !goods_pool_value.getValue("goods_parameter_supply_chain_goods_id").equals(""))
                            goods_parameter_supply_chain_goods_id = Long.valueOf(goods_pool_value.getString("goods_parameter_supply_chain_goods_id"));
                        else {
                            goods_parameter_supply_chain_goods_id = 0;
                        }

                        if (total_records == 0) {
                            // 新增
                            String insert_goods_parameter_sql = "INSERT INTO goods_parameter(goods_gather_area_id, class_lv1_id, class_lv2_id, variety_id, aggregation_id, goods_sales_grade_id, sales_package_spec_type_id, standard_safety_stock_days,\n" +
                            "                                temp_safety_stock_days, safety_stock_warn_days, spot_purchase_replenish_price_discount, spot_purchase_stock_price_discount, goods_parameter_list_goods_id, list_goods_id, list_goods_grade_id, sales_quantity_scheme_id, list_goods_price_mode_id, list_goods_specification_id,\n" +
                            "                                goods_parameter_supply_chain_goods_id, supply_chain_goods_id, supply_chain_goods_settle_package_spec, supply_chain_goods_grade_id, supply_chain_goods_price_mode_id, supply_chain_goods_specification_id,create_user)"
                            + " VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23,$24,$25)  RETURNING id";
                            String insert_goods_parameter_list_goods_sql = "INSERT INTO goods_parameter_list_goods(list_goods_status, list_package_spec, list_goods_weight_package_spec,sales_package_spec, " +
                            "list_goods_package_weight, list_goods_package_length, list_goods_package_width,  list_goods_package_height,list_goods_package_volume, list_goods_package_size,create_user)"
                            + " VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)  RETURNING id";
                            String insert_goods_parameter_supply_chain_goods_sql = "INSERT INTO goods_parameter_supply_chain_goods(supply_chain_goods_status, supply_chain_goods_algorithm_package_spec,supply_chain_goods_weight_package_spec, supply_chain_goods_package_weight," +
                            " supply_chain_goods_package_length, supply_chain_goods_package_width,  supply_chain_goods_package_height,supply_chain_goods_package_volume, supply_chain_goods_package_size,create_user)"
                            + " VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)  RETURNING id";

                            //事务控制
                            conn.begin().onSuccess(tx -> { 
                            	//新增商品名系统录入参数在线管控表 挂牌商品分表
                            	this.commonRepository.transactionInsertOrUpdate(conn,  insert_goods_parameter_list_goods_sql, Tuple.of(list_goods_status, list_package_spec, list_goods_weight_package_spec, sales_package_spec,
                            			list_goods_package_weight, list_goods_package_length, list_goods_package_width, list_goods_package_height, list_goods_package_volume, list_goods_package_size, user_id), "insert_goods_parameter_list_goods")
                            	.onSuccess(goods_parameter_list_goods_ids -> {   
                                     long new_goods_parameter_list_goods_id = goods_parameter_list_goods_ids.get(0);
                                     logger.info("新增商品名系统录入参数在线管控表 挂牌商品分表, new_goods_parameter_list_goods_id{}",new_goods_parameter_list_goods_id);
                                     //新增商品名系统录入参数在线管控表 供应链商品分表
                                      this.commonRepository.transactionInsertOrUpdate(conn,   insert_goods_parameter_supply_chain_goods_sql, Tuple.of(supply_chain_goods_status, supply_chain_goods_algorithm_package_spec, supply_chain_goods_weight_package_spec, supply_chain_goods_package_weight,
                                    		  supply_chain_goods_package_length, supply_chain_goods_package_width, supply_chain_goods_package_height, supply_chain_goods_package_volume, supply_chain_goods_package_size, user_id), "insert_goods_parameter")
                                      .onSuccess(goods_parameter_supply_chain_goods_ids -> {    
                                          long new_goods_parameter_supply_chain_goods_id =  goods_parameter_supply_chain_goods_ids.get(0);
                                          logger.info("新增商品名系统录入参数在线管控表 供应链商品分表,new_goods_parameter_supply_chain_goods_id {}",new_goods_parameter_supply_chain_goods_id);
                                          //新增商品名系统录入参数在线管控表 
                                          this.commonRepository.transactionInsertOrUpdate(conn, promise, insert_goods_parameter_sql, Tuple.of(goods_gather_area_id, class_lv1_id, class_lv2_id, variety_id, aggregation_id, goods_sales_grade_id, sales_package_spec_type_id, standard_safety_stock_days,
                                          temp_safety_stock_days, safety_stock_warn_days, spot_purchase_replenish_price_discount, spot_purchase_stock_price_discount, new_goods_parameter_list_goods_id, list_goods_id, list_goods_grade_id, sales_quantity_scheme_id, list_goods_price_mode_id, list_goods_specification_id,
                                          new_goods_parameter_supply_chain_goods_id, supply_chain_goods_id, supply_chain_goods_settle_package_spec, supply_chain_goods_grade_id, supply_chain_goods_price_mode_id, supply_chain_goods_specification_id, user_id), "insert_goods_parameter", true, tx);
                                           
                                      }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "insert_goods_parameter_supply_chain_goods", "服务器处理异常"))); 
                            	}).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "insert_goods_parameter_list_goods", "服务器处理异常"))); 
                            }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "insert_goods_parameter", "服务器处理异常")));
                        } else {
                            // 根据aggregation_id, supply_chain_goods_id, supply_chain_goods_settle_package_spec进行更新
                            String update_goods_parameter_sql = "UPDATE goods_parameter SET goods_gather_area_id = $1, class_lv1_id = $2, class_lv2_id = $3, variety_id = $4,aggregation_id=$5,goods_sales_grade_id = $6, sales_package_spec_type_id = $7, standard_safety_stock_days = $8, temp_safety_stock_days = $9," +
                            "safety_stock_warn_days = $10, spot_purchase_replenish_price_discount = $11, spot_purchase_stock_price_discount = $12,  list_goods_id = $13, list_goods_grade_id = $14, sales_quantity_scheme_id = $15, list_goods_price_mode_id = $16," +
                            "list_goods_specification_id = $17, supply_chain_goods_id = $18, supply_chain_goods_grade_id = $19,supply_chain_goods_price_mode_id = $20,supply_chain_goods_specification_id = $21, update_user = $22 WHERE " +
                            "goods_gather_area_id = $23 and aggregation_id = $24 and list_goods_id=$25 and supply_chain_goods_id=$26 and supply_chain_goods_settle_package_spec = $27 RETURNING id";
                            //根据goods_parameter_list_goods_id更新
                            String update_goods_parameter_list_goods_sql = "UPDATE goods_parameter_list_goods SET list_goods_status = $1, list_package_spec = $2, list_goods_weight_package_spec = $3, sales_package_spec = $4, list_goods_package_weight = $5,list_goods_package_length = $6, list_goods_package_width = $7, list_goods_package_height = $8, " +
                            "list_goods_package_volume = $9, list_goods_package_size = $10 ,update_user = $11 WHERE id = $12 RETURNING id";
                            //根据goods_parameter_supply_chain_goods_id更新
                            String update_goods_parameter_supply_chain_goods_sql = "UPDATE goods_parameter_supply_chain_goods SET supply_chain_goods_status = $1, supply_chain_goods_algorithm_package_spec = $2, supply_chain_goods_weight_package_spec = $3, supply_chain_goods_package_weight = $4,supply_chain_goods_package_length = $5," +
                            " supply_chain_goods_package_width = $6, supply_chain_goods_package_height = $7,supply_chain_goods_package_volume = $8, supply_chain_goods_package_size = $9 ,update_user = $10 WHERE id = $11 RETURNING id";
                            //事务控制
                            conn.begin().onSuccess(tx -> {
                                //更新商品名系统录入参数在线管控表
                                this.commonRepository.transactionInsertOrUpdate(conn, promise, update_goods_parameter_sql, Tuple.of(goods_gather_area_id, class_lv1_id, class_lv2_id, variety_id, aggregation_id, goods_sales_grade_id, sales_package_spec_type_id, standard_safety_stock_days, temp_safety_stock_days,
                                safety_stock_warn_days, spot_purchase_replenish_price_discount, spot_purchase_stock_price_discount, list_goods_id, list_goods_grade_id, sales_quantity_scheme_id, list_goods_price_mode_id, list_goods_specification_id,
                                supply_chain_goods_id, supply_chain_goods_grade_id, supply_chain_goods_grade_id, supply_chain_goods_specification_id, user_id, goods_gather_area_id, aggregation_id, list_goods_id, supply_chain_goods_id, supply_chain_goods_settle_package_spec), "update_goods_parameter", false, null);
                                logger.info("更新商品名系统录入参数在线管控表");
                                //更新商品名系统录入参数在线管控表 挂牌商品分表
                                this.commonRepository.transactionInsertOrUpdate(conn, promise, update_goods_parameter_list_goods_sql, Tuple.of(list_goods_status, list_package_spec, list_goods_weight_package_spec, sales_package_spec,
                                list_goods_package_weight, list_goods_package_length, list_goods_package_width, list_goods_package_height, list_goods_package_volume, list_goods_package_size, user_id, goods_parameter_list_goods_id), "update_goods_parameter_list_goods", false, null);
                                logger.info("更新商品名系统录入参数在线管控表 挂牌商品分表");
                                //更新商品名系统录入参数在线管控表 供应链商品分表
                                this.commonRepository.transactionInsertOrUpdate(conn, promise, update_goods_parameter_supply_chain_goods_sql, Tuple.of(supply_chain_goods_status, supply_chain_goods_algorithm_package_spec, supply_chain_goods_weight_package_spec, supply_chain_goods_package_weight,
                                supply_chain_goods_package_length, supply_chain_goods_package_width, supply_chain_goods_package_height, supply_chain_goods_package_volume, supply_chain_goods_package_size, user_id, goods_parameter_supply_chain_goods_id), "update_goods_parameter", true, tx);
                                logger.info("更新商品名系统录入参数在线管控表 供应链商品分表");

                            }).onFailure(err -> CommonUtils.createJsonObject(err, "saveCommandList", "服务器处理异常")); 
                        }
                    } else {
                        promise.fail(CommonUtils.createExceptionJsonObjectStr(ar, "insertGoodsParameter", "新增数据发生错误"));
                    }
                });
            } else if (method.equals("update")) {
                // 并行执行多个future
                CompositeFuture.join(Arrays.asList(getValueFromGoodsPoolFuture, goodsGatherAreaCodeFuture, getIdByCodeFuture, classLv1CodeFuture, classLv2CodeFuture, varietyCodeFuture, aggregationCodeFuture, gradeCodeFuture, salesPackageSpecTypeCodeFuture, listGoodsCodeFuture,
                listGoodsGradeCodeFuture, salesQuantitySchemeCodeFuture, listGoodsPriceModeCodeFuture, listGoodsSpecificationCodeFuture, supplyChainGoodsCodeFuture, supplyChainGoodsGradeCodeFuture, supplyChainGoodsPriceModeCodeFuture, supplyChainGoodsSpecificationCodeFuture)).onComplete(ar -> {
                    if (ar.succeeded()) {
                        JsonObject goods_pool_value = (JsonObject) ar.result().list().get(0);
                        //获取list_goods_status的值
                        int list_goods_status;
                        if (goods_pool_value.getValue("list_goods_status") != null && !goods_pool_value.getValue("list_goods_status").equals(""))
                            list_goods_status = Integer.valueOf(goods_pool_value.getString("list_goods_status"));
                        else {
                            list_goods_status = 0;
                        }
                        //获取supply_chain_goods_status的值
                        int supply_chain_goods_status;
                        if (goods_pool_value.getValue("supply_chain_goods_status") != null && !goods_pool_value.getValue("supply_chain_goods_status").equals(""))
                            supply_chain_goods_status = Integer.valueOf(goods_pool_value.getString("supply_chain_goods_status"));
                        else {
                            supply_chain_goods_status = 0;
                        }
                        //获取standard_safety_stock_days的值
                        double standard_safety_stock_days;
                        if (goods_pool_value.getValue("standard_safety_stock_days") != null && !goods_pool_value.getValue("standard_safety_stock_days").equals(""))
                            standard_safety_stock_days = Double.valueOf(goods_pool_value.getString("standard_safety_stock_days"));
                        else {
                            standard_safety_stock_days = 0;
                        }
                        //获取list_package_spec的值
                        double list_package_spec;
                        if (goods_pool_value.getValue("list_package_spec") != null && !goods_pool_value.getValue("list_package_spec").equals(""))
                            list_package_spec = Double.valueOf(goods_pool_value.getString("list_package_spec"));
                        else {
                            list_package_spec = 0;
                        }
                        //获取list_package_spec的值
                        double sales_package_spec;
                        if (goods_pool_value.getValue("sales_package_spec") != null && !goods_pool_value.getValue("sales_package_spec").equals(""))
                            sales_package_spec = Double.valueOf(goods_pool_value.getString("sales_package_spec"));
                        else {
                            sales_package_spec = 0;
                        }
                        //计算 list_goods_weight_package_spec
                        double list_goods_weight_package_spec = sales_package_spec + list_goods_package_weight;
                        String admit_time_line = "";

                        goods_pool_value = (JsonObject) ar.result().list().get(2);
                        long goods_parameter_list_goods_id;
                        if (goods_pool_value.getValue("goods_parameter_list_goods_id") != null && !goods_pool_value.getValue("goods_parameter_list_goods_id").equals(""))
                            goods_parameter_list_goods_id = Long.valueOf(goods_pool_value.getString("goods_parameter_list_goods_id"));
                        else {
                            goods_parameter_list_goods_id = 0;
                        }
                        long goods_parameter_supply_chain_goods_id;
                        if (goods_pool_value.getValue("goods_parameter_supply_chain_goods_id") != null && !goods_pool_value.getValue("goods_parameter_supply_chain_goods_id").equals(""))
                            goods_parameter_supply_chain_goods_id = Long.valueOf(goods_pool_value.getString("goods_parameter_supply_chain_goods_id"));
                        else {
                            goods_parameter_supply_chain_goods_id = 0;
                        }

                        // 根据id进行更新
                        String update_goods_parameter_sql = "UPDATE goods_parameter SET goods_gather_area_id = $1, class_lv1_id = $2, class_lv2_id = $3, variety_id = $4, goods_sales_grade_id = $5,sales_package_spec_type_id = $6, standard_safety_stock_days = $7, temp_safety_stock_days = $8, " +
                        "safety_stock_warn_days = $9, spot_purchase_replenish_price_discount = $10, spot_purchase_stock_price_discount = $11,  list_goods_id = $12, list_goods_grade_id = $13, sales_quantity_scheme_id = $14, list_goods_price_mode_id = $15, " +
                        "list_goods_specification_id = $16, supply_chain_goods_id = $17, supply_chain_goods_grade_id = $18,supply_chain_goods_price_mode_id = $19,supply_chain_goods_specification_id = $20,supply_chain_goods_settle_package_spec = $21, aggregation_id = $22,update_user = $23 WHERE id = $24 RETURNING id";
                        //根据goods_parameter_list_goods_id更新
                        String update_goods_parameter_list_goods_sql = "UPDATE goods_parameter_list_goods SET list_goods_status = $1, list_package_spec = $2, list_goods_weight_package_spec = $3, sales_package_spec = $4, list_goods_package_weight = $5,list_goods_package_length = $6, list_goods_package_width = $7, list_goods_package_height = $8, " +
                        "list_goods_package_volume = $9, list_goods_package_size = $10, update_user = $11 WHERE id = $12 RETURNING id";
                        //根据goods_parameter_supply_chain_goods_id更新
                        String update_goods_parameter_supply_chain_goods_sql = "UPDATE goods_parameter_supply_chain_goods SET supply_chain_goods_status = $1, supply_chain_goods_algorithm_package_spec = $2, supply_chain_goods_weight_package_spec = $3, supply_chain_goods_package_weight = $4,supply_chain_goods_package_length = $5," +
                        " supply_chain_goods_package_width = $6, supply_chain_goods_package_height = $7,supply_chain_goods_package_volume = $8, supply_chain_goods_package_size = $9 ,update_user = $10 WHERE id = $11 RETURNING id";
                        //事务控制
                        conn.begin().onSuccess(tx -> {
                            //更新商品名系统录入参数在线管控表
                            this.commonRepository.transactionInsertOrUpdate(conn, promise, update_goods_parameter_sql, Tuple.of(goods_gather_area_id, class_lv1_id, class_lv2_id, variety_id, goods_sales_grade_id, standard_safety_stock_days,sales_package_spec_type_id, temp_safety_stock_days,
                            safety_stock_warn_days, spot_purchase_replenish_price_discount, spot_purchase_stock_price_discount, list_goods_id, list_goods_grade_id, sales_quantity_scheme_id, list_goods_price_mode_id, list_goods_specification_id,
                            supply_chain_goods_id, supply_chain_goods_grade_id, supply_chain_goods_grade_id, supply_chain_goods_specification_id, supply_chain_goods_settle_package_spec,aggregation_id, user_id, id), "update_goods_parameter", false, null);
                            logger.info("更新商品名系统录入参数在线管控表");
                            //更新商品名系统录入参数在线管控表 挂牌商品分表
                            this.commonRepository.transactionInsertOrUpdate(conn, promise, update_goods_parameter_list_goods_sql, Tuple.of(list_goods_status, list_package_spec, list_goods_weight_package_spec, sales_package_spec,
                            list_goods_package_weight, list_goods_package_length, list_goods_package_width, list_goods_package_height, list_goods_package_volume, list_goods_package_size, user_id, goods_parameter_list_goods_id), "update_goods_parameter_list_goods", false, null);
                            logger.info("更新商品名系统录入参数在线管控表 挂牌商品分表");
                            //更新商品名系统录入参数在线管控表 供应链商品分表
                            this.commonRepository.transactionInsertOrUpdate(conn, promise, update_goods_parameter_supply_chain_goods_sql, Tuple.of(supply_chain_goods_status, supply_chain_goods_algorithm_package_spec, supply_chain_goods_weight_package_spec, supply_chain_goods_package_weight,
                            supply_chain_goods_package_length, supply_chain_goods_package_width, supply_chain_goods_package_height, supply_chain_goods_package_volume, supply_chain_goods_package_size, user_id, goods_parameter_supply_chain_goods_id), "update_goods_parameter", true, tx);
                            logger.info("更新商品名系统录入参数在线管控表 供应链商品分表");

                        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "saveCommandList", "服务器处理异常")));

                    } else {
                        promise.fail(CommonUtils.createExceptionJsonObjectStr(ar, "updateGoodsParameter", "更新数据发生错误"));
                    }
                });
            }
        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "getGoodsParameterRecords", "数据库连接失败")));
        return promise.future();
    }

}

 
